﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;

namespace EffiProzDBTest 
{
    /// <summary>
    /// Summary description for SubQueries
    /// </summary>
    [TestClass]
    public class SubQueries : BaseTest
    {
        public SubQueries()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion



        [TestMethod]
        public void TestLikePredicateOptimizations()
        {

            CleanUp("LikePredicateOptimizations");

            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "LikePredicateOptimizations",
                     user, password, props);

            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "create table test(name varchar(255))");
            Result ps = TestHelper.GetPrepareStatement(sessionProxy, "insert into test values(?)");

            for (int i = 0; i < 1000; i++)
            {
                TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { "name" + i });
            }

            Result result = TestHelper.ExecuteSelectSQL(sessionProxy, "select count(*) from test where name = null", null);
            Iterator it = result.iterator();
            int expectedValue = (int)((object[])it.next())[0];
            ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test where name like null");
             result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] {  });
             it = result.iterator();
            int actualValue = (int)((object[])it.next())[0];
            Assert.AreEqual(expectedValue, actualValue);

            result = TestHelper.ExecuteSelectSQL(sessionProxy, "select count(*) from test where name = ''", null);
             it = result.iterator();
             expectedValue = (int)((object[])it.next())[0];
            ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test where name like ''");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { });
            it = result.iterator();
             actualValue = (int)((object[])it.next())[0];
            Assert.AreEqual(expectedValue, actualValue);

            result = TestHelper.ExecuteSelectSQL(sessionProxy, "select count(*) from test where name is not null", null);
            it = result.iterator();
            expectedValue = (int)((object[])it.next())[0];
            ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test where name like '%'");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] {});
            it = result.iterator();
            actualValue = (int)((object[])it.next())[0];
            Assert.AreEqual(expectedValue, actualValue);

            result = TestHelper.ExecuteSelectSQL(sessionProxy, "select count(*) from test where left(name, 6) = 'name44'", null);
            it = result.iterator();
            expectedValue = (int)((object[])it.next())[0];
            ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test where name like 'name44%'");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { });
            it = result.iterator();
            actualValue = (int)((object[])it.next())[0];
            Assert.AreEqual(expectedValue, actualValue);

            result = TestHelper.ExecuteSelectSQL(sessionProxy, "select count(*) from test where left(name,5) = 'name4' and right(name,1) = 5", null);
            it = result.iterator();
            expectedValue = (int)((object[])it.next())[0];
            ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test where name like 'name4%5'");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { });
            it = result.iterator();
            actualValue = (int)((object[])it.next())[0];
            Assert.AreEqual(expectedValue, actualValue);


            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "drop table test1 if exists; CREATE TABLE test1 (col VARCHAR(30));");
            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO test1 (col) VALUES ('one'); ");

            ps = TestHelper.GetPrepareStatement(sessionProxy, "SELECT * FROM test1 WHERE ( col LIKE ? )");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { "one" });
            it = result.iterator();
            string sa = (string)((object[])it.next())[0];

            ps = TestHelper.GetPrepareStatement(sessionProxy, "SELECT * FROM test1 WHERE ( col LIKE 'one' )");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { });
            it = result.iterator();
            string se = (string)((object[])it.next())[0];

            Assert.AreEqual(se, sa);

        }


        [TestMethod]
        public void TestPreparedSubQueries3()
        {

            CleanUp("TestPreparedSubQueries3");

            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "TestPreparedSubQueries3",
                     user, password, props);

            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "create table test(id int)");
            Result ps = TestHelper.GetPrepareStatement(sessionProxy, "insert into test values(?)");

            for (int i = 0; i < 10; i++)
            {
                TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { i });
            }

            ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test where id in(?,?)");

            Result result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 0, 9 });

            Iterator it = result.iterator();
            int actualValue = (int)((object[])it.next())[0];

            Assert.AreEqual(2, actualValue);

           result = TestHelper.ExecuteSelectSQL(sessionProxy, "select count(*) from test a, test b where 0 in(a.id, b.id)",null);
           it = result.iterator();
           int expectedValue =(int) ((object[]) it.next())[0];

           ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test a, test b where ? in (a.id, b.id)");
           result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 0 });
           it = result.iterator();
           actualValue = (int)((object[])it.next())[0];

           Assert.AreEqual(expectedValue, actualValue);

           try
           {
               ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test a, test b where ? in(?, b.id)");
               Assert.Fail("expected exception preparing ");
           }
           catch (Exception)
           {
           }

           try
           {
               ps = TestHelper.GetPrepareStatement(sessionProxy, "select count(*) from test a, test b where a.id in(?, ?)");
             
           }
           catch (Exception)
           {
               Assert.Fail("unexpected exception preparing ");
           }

           sql = "select count(*) from "
            + "(select * from test where id in (1,2)) a,"
            + "(select * from test where id in (3,4)) b "
            + "where a.id < 2 and b.id < 4";

           result = TestHelper.ExecuteSelectSQL(sessionProxy, sql, null);
           it = result.iterator();
           expectedValue = (int)((object[])it.next())[0];

           sql = "select count(*) from "
            + "(select * from test where id in (?,?)) a,"
            + "(select * from test where id in (?,?)) b "
            + "where a.id < ? and b.id < ?";

           ps = TestHelper.GetPrepareStatement(sessionProxy, sql);
           result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 1,2,3,4,2,4 });
           it = result.iterator();
           actualValue = (int)((object[])it.next())[0];

           Assert.AreEqual(expectedValue, actualValue);

        }


        [TestMethod]
        public void TestPreparedSubQueries2()
        {

            CleanUp("TestPreparedSubQueries2");

            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "TestPreparedSubQueries2",
                     user, password, props);

            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "create table t(i decimal)");
            Result ps = TestHelper.GetPrepareStatement(sessionProxy, "insert into t values(?)");

            for (int i = 0; i < 100; i++)
            {      
                TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[]{i});
            }

            ps = TestHelper.GetPrepareStatement(sessionProxy, "select * from (select * from t where i < ?)");

            Result result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 4 });
            TestHelper.MatchMultiResult(result, new object[,] { { 0 }, { 1 }, { 2 }, { 3 } });

            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 5 });
            TestHelper.MatchMultiResult(result, new object[,] { {0}, {1}, {2}, {3},{4} });

            ps = TestHelper.GetPrepareStatement(sessionProxy, "select sum(i) from (select i from t where i between ? and ?)");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 4 , 5});
            TestHelper.MatchMultiResult(result, new object[,] { { (decimal)9 } });

            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 4, 6 });
            TestHelper.MatchMultiResult(result, new object[,] { { (decimal)15 } });

            ps = TestHelper.GetPrepareStatement(sessionProxy, "select * from (select i as c1 from t where i < ?) a, (select i as c2 from t where i < ?) b");
            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 1, 1 });
            TestHelper.MatchMultiResult(result, new object[,] { { 0, 0 } });

            result = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { 2, 2 });
            TestHelper.MatchMultiResult(result, new object[,] { { 0, 0 }, { 0, 1 }, { 1, 0 }, { 1, 1 } });

        }



        [TestMethod]
        public void TestPreparedSubQueries()
        {

            CleanUp("TestPreparedSubQueries");
            
            SessionInterface sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "TestPreparedSubQueries",
                     user, password, props);

            Result rout = null;
            for (int i = 0; i < stmtArray.Length; i++)
            {
        
                if (stmtArray[i].prepare)
                {
                    Result ps = null;

                    ps = TestHelper.GetPrepareStatement(sessionProxy, stmtArray[i].command);

                    rout = TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, stmtArgs[i]);

                }
                else
                {
                    rout = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, stmtArray[i].command);
                }
            }

        }

        private Object[][] stmtArgs = {
        new object[]{},   new object[]{},   new object[]{},   new object[]{},   new object[]{}, 
        new object[]{},   new object[]{},   new object[]{},   new object[]{}, new object[]{} ,   new object[]{ (2) },
          new object[]{ (2) }
                                      };

        private sqlStmt[] stmtArray = {
        new sqlStmt("drop table a if exists", false, false),
        new sqlStmt("create cached table a (a int identity,b int)", false,
                    false),
        new sqlStmt("create index bIdx on a(b)", false, false),
        new sqlStmt("insert into a(b) values(1)", true, true),
        new sqlStmt("insert into a(b) values(2)", true, true),
        new sqlStmt("insert into a(b) values(3)", true, true),
        new sqlStmt("insert into a(b) values(4)", true, true),
        new sqlStmt("insert into a(b) values(5)", true, true),
        new sqlStmt("insert into a(b) values(6)", true, true),
        new sqlStmt(
            "update a set b=100 where b>(select b from a X where X.a=2)",
            true, true),
        new sqlStmt("update a set b=200 where b>(select b from a where a=?)",
                    true, true),
        new sqlStmt(
            "update a set b=300 where b>(select b from a X where X.a=?)",
            true, true)
    };


        private class sqlStmt
        {

            public bool prepare;
            public bool update;
            public  String command;

            public sqlStmt(String c, bool p, bool u)
            {

                prepare = p;
                command = c;
                update = u;
            }
        }
    }
}
